package test.testList;

import org.apache.commons.codec.binary.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ExcelToConstants {
    private static final Logger logger = LoggerFactory.getLogger(ExcelToConstants.class);

    public static void readExcel(String filePath, int startRow, boolean isExcel2003) throws Exception {
        InputStream is = null;
        try {
            /** 创建读取文件的输入流 */
            is = new FileInputStream(new File(filePath));
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            int numberOfSheets = wb.getNumberOfSheets();
            /** 调用本类的读取方法读取excel数据 */
            for (int i = 0; i < numberOfSheets; i++) {
                read(wb, startRow, i);
            }
        } catch (Exception ex) {
            logger.error("读取excel文件异常!", ex);
            ex.printStackTrace();
            throw ex;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void read(Workbook wb, int startRow, int sheetIndex) throws Exception {
        /** 总列数 */
        int totalCells = 0;
        /** 创建集合存储读取的数据 */
        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(sheetIndex);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        String outputFilePath = "E:/workTest/src/main/java/test/dict/" + sheet.getSheetName() + ".java";
        FileOutputStream fileOutputStream = new FileOutputStream(new File(outputFilePath));
        //输出class名
        String className = "public class " + sheet.getSheetName() + " {";
        fileOutputStream.write(className.getBytes());

        //换行
        String newLine = System.getProperty("line.separator");
        fileOutputStream.write(newLine.getBytes());
        fileOutputStream.write(newLine.getBytes());

        /** 循环Excel的行 */
        for (int r = startRow; ; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                break;
            }
            short firstCellNum = row.getFirstCellNum();

            String firstCellValue = getCellValue(row.getCell(firstCellNum));
            if(firstCellValue == null || firstCellValue == ""){
                break;
            }

            if(r == startRow){
                String cellValue1 = getCellValue(row.getCell(1));
                String annotation = "    // " + cellValue1;
                fileOutputStream.write(annotation.getBytes());
                fileOutputStream.write(newLine.getBytes());
                String enumName = "    public static final String " + firstCellValue + " = \"" + firstCellValue +"\";";
                fileOutputStream.write(enumName.getBytes());
                fileOutputStream.write(newLine.getBytes());
                fileOutputStream.write(newLine.getBytes());
            }

            if(r>startRow){
                Row beforeRow = sheet.getRow(r-1);
                String beforeCellValue = getCellValue(beforeRow.getCell(beforeRow.getFirstCellNum()));
                if(!StringUtils.equals(beforeCellValue,firstCellValue)){
                    String cellValue1 = getCellValue(row.getCell(1));
                    String annotation = "    // " + cellValue1;
                    fileOutputStream.write(annotation.getBytes());
                    fileOutputStream.write(newLine.getBytes());
                    String enumName = "    public static final String " + firstCellValue + " = \"" + firstCellValue +"\";";
                    fileOutputStream.write(enumName.getBytes());
                    fileOutputStream.write(newLine.getBytes());
                    fileOutputStream.write(newLine.getBytes());
                }
            }
        }
        String lastClass = "}";
        fileOutputStream.write(lastClass.getBytes());
        fileOutputStream.write(newLine.getBytes());

        //关流
        fileOutputStream.close();

    }

    private static String getCellValue(Cell cell){
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: // 数字
                    // 判断是不是日期格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue() + "";
                    }else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                    break;
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    cellValue = "";
                    break;
                case ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }
}
